package sqlClass.daoClass;

import general.appClass.StatisticApp;
import general.dbClass.DBCategory;
import general.dbClass.DBExif;
import general.dbClass.DBKeyword;
import general.dbClass.DBPicInfo;
import general.dbClass.DBThumbInfo;

import java.awt.Graphics;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.imageio.ImageIO;

import oracle.sql.BLOB;

public class DaoGet {

	public static boolean testUser(Connection conn, String username,
			String password) throws SQLException {
		String sql = "select username,password from DBUser";
		ResultSet rs = null;
		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		if (rs.next()) {
			if (username.equals(rs.getString("username"))
					&& password.equals(rs.getString("password"))) {
				return true;
			}
		}
		return false;
	}
	
	/**
	 * get all keywords from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return all keywords from database
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBKeyword> getAllKeywords(Connection conn)
			throws SQLException {
		String sql = "select kwnr,word from DBKeyword";
		ArrayList<DBKeyword> ret = new ArrayList<DBKeyword>();
		ResultSet rs = null;
		Statement stmt = conn.createStatement();

		rs = stmt.executeQuery(sql);
		while (rs.next()) {
			DBKeyword tmp = new DBKeyword(rs.getInt("kwnr"),
					rs.getString("word"));
			ret.add(tmp);
		}
		rs.close();
		stmt.close();

		return ret;

	}

	/**
	 * get all categories from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return all categories from database
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBCategory> getAllCategories(Connection conn)
			throws SQLException {
		String sql = "select ktnr,name from DBKategorie order by ktnr asc";
		ArrayList<DBCategory> ret = new ArrayList<DBCategory>();
		ResultSet rs = null;
		Statement stmt = conn.createStatement();

		rs = stmt.executeQuery(sql);
		while (rs.next()) {
			DBCategory tmp = new DBCategory(rs.getInt("ktnr"),
					rs.getString("name"));
			ret.add(tmp);
		}
		rs.close();
		stmt.close();

		return ret;
	}

	/**
	 * get image by name
	 * 
	 * @param conn
	 *            connection with database
	 * @param name
	 *            image's name
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return images
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getThumbsByName(Connection conn,
			String name, boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;

		if (isLogin)
			sql = "select bnr from DBPic where name=?";
		else
			sql = "select DBPic.bnr "
					+ "from DBPic,DBThumb "
					+ "where DBPic.bnr=DBThumb.bnr and DBThumb.thumb_lock=0 and DBPic.name=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		rs = pstmt.executeQuery();

		while (rs.next()) {
			DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"), null);
			ret.add(tmp);
		}

		rs.close();
		pstmt.close();
		return ret;
	}

	/**
	 * get thumbs in one category
	 * 
	 * @param conn
	 *            connection with database
	 * @param ktnr
	 *            category's id
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return thumbs
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getThumbsByCategory(Connection conn,
			int ktnr, boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;

		if (isLogin)
			sql = "select DBPic.bnr,DBPic.name " + "from DBPic,Zuordnen "
					+ "where " + "DBPic.bnr=Zuordnen.bnr and Zuordnen.ktnr=?";
		else
			sql = "select DBPic.bnr,DBPic.name "
					+ "from Zuordnen,DBPic,DBThumb " + "where "
					+ "DBPic.bnr=Zuordnen.bnr and DBPic.bnr=DBThumb.bnr and "
					+ "DBThumb.thumb_lock=0 and Zuordnen.ktnr=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, ktnr);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"),
					rs.getString("name"));
			ret.add(tmp);
		}
		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * get thumbs in keyword by word
	 * 
	 * @param conn
	 *            connection with database
	 * @param kwnr
	 *            keywords's word
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return thumbs
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getThumbsByKeyword(Connection conn,
			String word, boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;

		if (isLogin)
			sql = "select DBPic.bnr,DBPic.name "
					+ "from DBPic,Haben,DBKeyword "
					+ "where "
					+ "DBPic.bnr=Haben.bnr and Haben.kwnr=DBKeyword.kwnr and DBKeyword.word=?";
		else
			sql = "select DBPic.bnr,DBPic.name "
					+ "from Haben,DBPic,DBThumb,DBKeyword "
					+ "where "
					+ "DBPic.bnr=Haben.bnr and DBPic.bnr=DBThumb.bnr and Haben.kwnr=DBKeyword.kwnr and "
					+ "DBThumb.thumb_lock=0 and DBKeyword.word=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, word);
		rs = pstmt.executeQuery();

		while (rs.next()) {
			DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"),
					rs.getString("name"));
			ret.add(tmp);
		}
		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * get thumbs in keyword by id
	 * 
	 * @param conn
	 *            connection with database
	 * @param kwnr
	 *            keywords's id
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return thumbs
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getThumbsByKeywordId(Connection conn,
			int kwnr, boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;

		if (isLogin)
			sql = "select DBPic.bnr,DBPic.name " + "from DBPic,Haben "
					+ "where " + "DBPic.bnr=Haben.bnr and Haben.kwnr=?";
		else
			sql = "select DBPic.bnr,DBPic.name " + "from Haben,DBPic,DBThumb "
					+ "where "
					+ "DBPic.bnr=Haben.bnr and DBPic.bnr=DBThumb.bnr and "
					+ "DBThumb.thumb_lock=0 and Haben.kwnr=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, kwnr);
		rs = pstmt.executeQuery();

		while (rs.next()) {
			DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"),
					rs.getString("name"));
			ret.add(tmp);
		}
		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * 
	 * @param conn
	 * @param map
	 * @param isLogin
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("rawtypes")
	public static ArrayList<DBThumbInfo> getThumbsByExif(Connection conn,
			HashMap map, boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;
		int flag = 0;

		if (isLogin) {
			sql = "select DBExif.bnr from DBExif";

			Iterator iter = map.entrySet().iterator();
			while (iter.hasNext()) {
				Map.Entry entry = (Map.Entry) iter.next();
				String key = (String) entry.getKey();
				String val = (String) entry.getValue();

				if (val != null && !val.equals("")) {
					if (flag == 0) {
						sql += " where " + key + " = '" + val + "'";
						flag = 1;
					} else {
						sql += " and " + key + " = '" + val + "'";
					}
				}
			}
		}

		else {
			sql = "select DBExif.bnr from DBExif,DBThumb where DBExif.bnr = DBThumb.bnr  and DBThumb.thumb_lock=0";

			Iterator iter = map.entrySet().iterator();
			while (iter.hasNext()) {
				Map.Entry entry = (Map.Entry) iter.next();
				String key = (String) entry.getKey();
				String val = (String) entry.getValue();

				if (val != null && !val.equals(""))
					sql += " and " + key + " = '" + val + "'";
			}
		}

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			int id = rs.getInt("bnr");
			DBThumbInfo thumb = new DBThumbInfo(id, null);
			ret.add(thumb);
		}

		rs.close();
		stmt.close();

		return ret;
	}

	/**
	 * get all thumb's information from database.for anonymous get all unlocked
	 * thumb's information.
	 * 
	 * @param conn
	 *            connection with database
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return thumbs
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getAllThumbInfos(Connection conn,
			boolean isLogin) throws SQLException {
		String sql = "";
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;

		if (isLogin)
			sql = "select bnr,name " + "from DBPic";
		else
			sql = "select bnr,name " + "from DBPic natural join DBThumb "
					+ "where DBThumb.thumb_lock=0";

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"),
					rs.getString("name"));
			ret.add(tmp);
		}
		rs.close();
		stmt.close();

		return ret;
	}

	/**
	 * get the real thumb date from database
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            thumb's id
	 * @return thumb date
	 * @throws SQLException
	 *             SQL communication failed
	 * @throws IOException
	 */
	public static BufferedImage getThumbDateById(Connection conn, int nr)
			throws SQLException, IOException {
		BLOB blob = null;
		String sql = "select thumb from DBThumb where bnr=?";
		ResultSet rs = null;

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, nr);
		long beginTime = new Date().getTime();
		rs = pstmt.executeQuery();
		long endTime = new Date().getTime();
		if (rs.next()) {
			blob = (BLOB) rs.getBlob(1);
		}

		rs.close();
		pstmt.close();

		InputStream is = blob.getBinaryStream();
		BufferedImage img = ImageIO.read(is);
		is.close();
		long loadTime = endTime - beginTime;
		StatisticApp.query("thumb", "sql", blob.length(), loadTime, 1);
		return img;
	}

	/**
	 * get the real image date from database
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return image's date
	 * @throws SQLException
	 *             SQL communication failed
	 * @throws IOException
	 */
	public static BufferedImage getPicDateById(Connection conn, int nr,
			String size) throws SQLException, IOException {
		BLOB blob = null;
		String sql = "select bild from DBPic where bnr=?";
		ResultSet rs = null;

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, nr);
		rs = pstmt.executeQuery();

		if (rs.next()) {
			blob = (BLOB) rs.getBlob(1);
		}
		rs.close();
		pstmt.close();

		InputStream is = blob.getBinaryStream();
		BufferedImage img = ImageIO.read(is);
		is.close();

		if (size.equals("full"))
			return img;
		else {
			int scaledWidth = 0;
			int scaledHeight = 0;
			float divRate = 600 / 450;
			float imgRate = (float) img.getWidth() / (float) img.getHeight();

			if (imgRate > divRate) {
				scaledWidth = 600;
				scaledHeight = (int) (600 / imgRate);
			} else {
				scaledHeight = 450;
				scaledWidth = (int) (450 * imgRate);
			}
			Image tmp = img.getScaledInstance(scaledWidth, scaledHeight,
					Image.SCALE_SMOOTH);
			BufferedImage ret = new BufferedImage(scaledWidth, scaledHeight,
					BufferedImage.TYPE_INT_RGB);
			Graphics g = ret.getGraphics();
			g.drawImage(tmp, 0, 0, null);
			return ret;
		}
	}

	/**
	 * get the information of the image
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return the information of the image
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static DBPicInfo getPicInfoById(Connection conn, int nr)
			throws SQLException {
		// read the name and rate from database
		DBPicInfo ret = null;
		ResultSet rs = null;
		String sql = "select * from DBPic where bnr=?";

		String name = "";
		int rate = 0;
		DBExif exif = null;
		ArrayList<DBCategory> categories = new ArrayList<DBCategory>();
		ArrayList<DBKeyword> keywords = new ArrayList<DBKeyword>();

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, nr);

		rs = pstmt.executeQuery();

		if (rs.next()) {
			name = rs.getString("name");
			rate = rs.getInt("rate");
		}
		rs.close();
		pstmt.close();

		// read the relationship with category from database
		categories = getCategoriesByPic(conn, nr);

		// read the relationship with keyword from database
		keywords = getKeywordsByPic(conn, nr);

		// read the Exif Information from database
		exif = getExifByPic(conn, nr);

		ret = new DBPicInfo(nr, name, rate, exif, categories, keywords);
		return ret;
	}

	/**
	 * get lock status of the image from database
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return free status,free for false locked for true
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static int isPicLockedById(Connection conn, int nr)
			throws SQLException {
		int ret = 1;
		ResultSet rs = null;
		String sql = "Select thumb_lock from DBThumb where bnr=?";

		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, nr);

		rs = psmt.executeQuery();

		if (rs.next()) {
			ret = rs.getInt("thumb_lock");
		}

		rs.close();
		return ret;
	}

	/**
	 * get all camera model from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return camera models
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllCameraModel(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select model from DBExif where model is not null group by model";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("model"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all camera make from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return camera makes
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllCameraMake(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select make from DBExif where make is not null group by make";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("make"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all orientation from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return orientations
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllOrientation(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select orientation from DBExif where orientation is not null group by orientation";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("orientation"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all exposure time from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return exposure time
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllExposureTime(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select exposureTime from DBExif where exposureTime is not null group by exposureTime";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("exposureTime"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all fnumber from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return f-number
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllFnumber(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select fnumber from DBExif where fnumber is not null group by fnumber";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("fnumber"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all exposure programm from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return exposure programm
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllExposureProgram(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select exposureProgram from DBExif where exposureProgram is not null group by exposureProgram";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("exposureProgram"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all color space from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return color space
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllColorSpace(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select colorSpace from DBExif where colorSpace is not null group by colorSpace";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("colorSpace"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get all exposure mode from database
	 * 
	 * @param conn
	 *            connection with database
	 * @return exposure mode
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<String> getAllExposureMode(Connection conn)
			throws SQLException {
		ArrayList<String> ret = new ArrayList<String>();
		String sql = "select exposureMode from DBExif where exposureMode is not null group by exposureMode";
		ResultSet rs = null;

		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);

		while (rs.next()) {
			ret.add(rs.getString("exposureMode"));
		}

		rs.close();
		stmt.close();
		return ret;
	}

	/**
	 * get Exif information from database
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return the image's exif information, if there's no exif information for
	 *         the image,return a blank DBexif Object
	 * @throws SQLException
	 *             SQL communication failed
	 */
	private static DBExif getExifByPic(Connection conn, int nr)
			throws SQLException {
		String imgHeight = "";
		String imgWidth = "";
		String exposureTime = "";
		String fnumber = "";
		String exposureProgram = "";
		String timeOriginal = "";
		String timeDigitized = "";
		String colorSpace = "";
		String exifImgWidth = "";
		String exifImgHeight = "";
		String exposureMode = "";
		String make = "";
		String model = "";
		String orientation = "";
		String xResolution = "";
		String yResolution = "";

		ResultSet rs = null;
		String sql = "select * from DBExif where bnr=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, nr);
		rs = pstmt.executeQuery();

		if (rs.next()) {
			imgHeight = rs.getString("imgHeight");
			imgWidth = rs.getString("imgWidth");
			exposureTime = rs.getString("exposureTime");
			fnumber = rs.getString("fnumber");
			exposureProgram = rs.getString("exposureProgram");
			timeOriginal = rs.getString("timeOriginal");
			timeDigitized = rs.getString("timeDigitized");
			colorSpace = rs.getString("colorSpace");
			exifImgWidth = rs.getString("exifImgWidth");
			exifImgHeight = rs.getString("exifImgHeight");
			exposureMode = rs.getString("exposureMode");
			make = rs.getString("make");
			model = rs.getString("model");
			orientation = rs.getString("orientation");
			xResolution = rs.getString("xResolution");
			yResolution = rs.getString("yResolution");
		}

		rs.close();
		pstmt.close();
		return new DBExif(imgHeight, imgWidth, exposureTime, fnumber,
				exposureProgram, timeOriginal, timeDigitized, colorSpace,
				exifImgWidth, exifImgHeight, exposureMode, make, model,
				orientation, xResolution, yResolution);
	}

	/**
	 * get the categories from database ,which is related with the image
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return related categories
	 * @throws SQLException
	 *             SQL communication failed
	 */
	private static ArrayList<DBCategory> getCategoriesByPic(Connection conn,
			int nr) throws SQLException {
		ArrayList<DBCategory> ret = new ArrayList<DBCategory>();
		String sql = "select category.ktnr,category.name "
				+ "from DBKategorie category,Zuordnen zu "
				+ "where category.ktnr=zu.ktnr and zu.bnr=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, nr);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			DBCategory category = new DBCategory(rs.getInt("ktnr"),
					rs.getString("name"));
			ret.add(category);
		}
		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * get the keywords form database,which is related with the image
	 * 
	 * @param conn
	 *            connection with database
	 * @param nr
	 *            image's id
	 * @return related keywords
	 * @throws SQLException
	 *             SQL communication failed
	 */
	private static ArrayList<DBKeyword> getKeywordsByPic(Connection conn, int nr)
			throws SQLException {
		ArrayList<DBKeyword> ret = new ArrayList<DBKeyword>();
		String sql = "select keyword.kwnr,keyword.word "
				+ "from DBKeyword keyword,Haben haben "
				+ "where keyword.kwnr=haben.kwnr and haben.bnr=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, nr);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			DBKeyword keyword = new DBKeyword(rs.getInt("kwnr"),
					rs.getString("word"));
			ret.add(keyword);
		}
		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * get image by name
	 * 
	 * @param conn
	 *            connection with database
	 * @param name
	 *            image's name
	 * @param isLogin
	 *            user's permission,true for registered user,false for anonymous
	 * @return images
	 * @throws SQLException
	 *             SQL communication failed
	 */
	public static ArrayList<DBThumbInfo> getThumbsBySearch(Connection conn,
			String sql, boolean isLogin) throws SQLException {
		ArrayList<DBThumbInfo> ret = new ArrayList<DBThumbInfo>();
		ResultSet rs = null;
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if (sql != null) {
			rs = pstmt.executeQuery();
			while (rs.next()) {
				DBThumbInfo tmp = new DBThumbInfo(rs.getInt("bnr"),
						rs.getString("name"));
				ret.add(tmp);
			}

			rs.close();
			pstmt.close();
		}

		return ret;
	}
}
